package com.raidroid.rdwhois;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {
	public static final String KEY_ROWID = "_ID";
	public static final String KEY_TITLE = "TITLE";
	public static final String KEY_SERVER = "SERVER";
    public static final String KEY_PORT = "PORT";
    public static final String KEY_DESCRIPTION = "DESCRIPTION";   
    private static final String DATABASE_NAME = "WtDb";
    private static final String DATABASE_TABLE = "SERVERS";
    private static final String WORDS_DATABASE_TABLE = "WORDS";
    private static final int DATABASE_VERSION = 6;
    public static final String DB_PATH = "/data/data/com.raidroid.rdwhois/databases/";

    private static final String DATABASE_CREATE =
        "create table if not exists SERVERS (_ID integer primary key autoincrement, "
	    	        + "TITLE text not null, SERVER text not null, PORT text not null, " 
	    	        + "DESCRIPTION text not null);";
    
    private static final String WORDS_DATABASE_CREATE =
    	"create table if not exists WORDS (_ID integer primary key autoincrement, "
        + "TITLE text not null);";
        
    private final Context context;    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

	public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {    	
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
            db.execSQL(WORDS_DATABASE_CREATE);
       
            ContentValues initialValues;
            
            Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.internic.net'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		        	initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "Internic");
		            initialValues.put(KEY_SERVER, "whois.internic.net");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for .aero, .arpa, .biz, .com, .coop, .edu, .info, .int, .museum, .name, .net, or .org domain names.");
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}
        	}
                  
        	mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.godaddy.com'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		            initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "GoDaddy");
		            initialValues.put(KEY_SERVER, "whois.godaddy.com");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for godaddy.com domain names.");
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}
        		
        	}
            
        	mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.register.com'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		            initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "Register.com");
		            initialValues.put(KEY_SERVER, "whois.register.com");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for register.com domain names.");
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}        		
        	}
            
        	mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.tucows.com'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		            initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "TuCows");
		            initialValues.put(KEY_SERVER, "whois.tucows.com");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for tucows.com domain names.");
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}        	
        	}
            
        	mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.networksolutions.com'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		            initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "Network-Solutions");
		            initialValues.put(KEY_SERVER, "whois.networksolutions.com");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for networksolutions.com domain names.");
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}        		
        	}
            
        	mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.nic.tr'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		            initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "Nic.tr");
		            initialValues.put(KEY_SERVER, "whois.nic.tr");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for nic.tr domain names.");        
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}
        	}
            
        	mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_SERVER
                		}, 
                		KEY_SERVER + "='whois.nic.uk'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        	if(mCursor != null)
        	{
        		if(mCursor.getCount() <= 0){
		            initialValues = new ContentValues();
		            initialValues.put(KEY_TITLE, "Nominet");
		            initialValues.put(KEY_SERVER, "whois.nic.uk");
		            initialValues.put(KEY_PORT, "43");
		            initialValues.put(KEY_DESCRIPTION, "Query for nic.uk domain names.");        
		            db.insert(DATABASE_TABLE, null, initialValues);
        		}        		
        	}
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
        {            
            /*db.execSQL("DROP TABLE IF EXISTS SERVERS");*/
        	/*db.execSQL("DROP TABLE IF EXISTS WORDS");*/
            onCreate(db);
        }        
    }
    
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    public void close() 
    {
        DBHelper.close();
    }
    
    public long insertServer(String title, String server, int port, String description) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_SERVER, server);
        initialValues.put(KEY_PORT, port);
        initialValues.put(KEY_DESCRIPTION, description);
        return db.insert(DATABASE_TABLE, null, initialValues);
    }
    
    public long insertWord(String title) 
    {
    	if(title.trim() != ""){
	    	Cursor mCursor =
	            db.query(true, WORDS_DATABASE_TABLE, new String[] {
	            		KEY_TITLE
	            		}, 
	            		KEY_TITLE + "='" + title + "'", 
	            		null,
	            		null, 
	            		null, 
	            		null, 
	            		null);
	    	if(mCursor != null)
	    	{
	    		if(mCursor.getCount() <= 0){
			        ContentValues initialValues = new ContentValues();
			        initialValues.put(KEY_TITLE, title);
			        return db.insert(WORDS_DATABASE_TABLE, null, initialValues);
	    		}
	    	}
    	}
		return 0;
    }

    public boolean deleteServer(long rowId) 
    {
        return db.delete(DATABASE_TABLE, KEY_ROWID + 
        		"=" + rowId, null) > 0;
    }
    
    public boolean deleteAllWords() 
    {
        return db.delete(WORDS_DATABASE_TABLE, null, null) > 0;
    }

    public Cursor getAllServers() 
    {
        return db.query(DATABASE_TABLE, new String[] {
        		KEY_ROWID,
        		KEY_TITLE,
        		KEY_SERVER,
        		KEY_PORT,
                KEY_DESCRIPTION}, 
                null, 
                null, 
                null, 
                null, 
                null);
    }
    
    public Cursor getAllWords() 
    {
        return db.query(WORDS_DATABASE_TABLE, new String[] {
        		KEY_ROWID,
        		KEY_TITLE}, 
                null, 
                null, 
                null, 
                null, 
                KEY_TITLE);
    }

    public Cursor getServer(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_ROWID,
                		KEY_TITLE,
                		KEY_SERVER, 
                		KEY_PORT,
                		KEY_DESCRIPTION
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    public Cursor getWord(String word) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_TITLE
                		}, 
                		KEY_TITLE + "='" + word + "'", 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public boolean updateServer(long rowId, String title, String server, 
    int port, String description) 
    {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_SERVER, server);
        args.put(KEY_PORT, port);
        args.put(KEY_DESCRIPTION, description);
        return db.update(DATABASE_TABLE, args, 
                         KEY_ROWID + "=" + rowId, null) > 0;
    }
}
